package qwqw;
import java.lang.reflect.Array;
import java.sql.Connection;
import Entity.*;

import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;

import com.sun.jmx.snmp.Timestamp;

import Entity.ReqForm;


public class Sql_conn { 
	
			private Statement stmt;
			private ResultSet rs = null;
			private Object result;
		
		    private Connection conn;
			
			
			public Sql_conn()
			{
				try 
				{
		            Class.forName("com.mysql.jdbc.Driver").newInstance();
		        } catch (Exception ex) {/* handle the error*/}
		        
		       try 
		        {
		           conn = DriverManager.getConnection("jdbc:mysql://localhost/icmproject","root","Braude");
		            System.out.println("SQL connection succeed");
		     	} catch (SQLException ex) 
		     	    {/* handle any errors*/
		            System.out.println("SQLException: " + ex.getMessage());
		           System.out.println("SQLState: " + ex.getSQLState());
		            System.out.println("VendorError: " + ex.getErrorCode());
		           }
		   	}
			

		
				
			
			


	
	public Object get_current_late_requests (){
	
	  ArrayList<Late_requests> late_requests = new ArrayList<Late_requests>();
	try {
		stmt=conn.createStatement();

// creating view that will contain expected closure date and the closure date
		stmt.executeUpdate("drop view dates");
		stmt.executeUpdate("create view dates as SELECT DATE(openDate+ INTERVAL  duration DAY )as new_date,closureDate,r_id,stageID from stageinreq");
// create view with all the late requests		
		stmt.executeUpdate("drop view late_req");
		stmt.executeUpdate("create view late_req as select r_id,stageID,new_date,closureDate from dates where new_date < current_date() and closureDate is null");
// create view of request_id, the latest date we updated new status		
		stmt.executeUpdate("drop view req_max_dates");
		stmt.executeUpdate("create view req_max_dates as select r_id,max(reqDate) as max_date from requeststatus group by r_id");
// create view with all the current frozen requests		
		stmt.executeUpdate("drop view frozen_req");
		stmt.executeUpdate("create view frozen_req as select requeststatus.r_id from requeststatus,req_max_dates where requeststatus.r_id=req_max_dates.r_id and requeststatus.reqStatus='FROZEN' and requeststatus.reqDate=req_max_dates.max_date");
// create view with all the delay requests and the number of the dayes in late
		
// select all the late requests without the frozen requests		
		rs=stmt.executeQuery( "select late_req.r_id as r_id,late_req.stageID as stage_id,current_date()-late_req.new_date as late_dur,request.in_system as in_sys	from late_req,request where late_req.r_id=request.r_id and late_req.r_id not in(select* from frozen_req) and late_req.closureDate is null");
		
// sr will contain for each information system the number of lates and the sum of all lates
		
	  while (rs.next()){
		  Late_requests l_t=new Late_requests();
		  l_t.setReq_id(rs.getInt("r_id"));
		  l_t.setLate_duration(rs.getInt("late_dur")) ;
		  l_t.setStage_id(rs.getInt("stage_id"));
		  late_requests.add(l_t);}
		  result=late_requests;}
	catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} return result;
	} 
	
	public Object viewReq(int req_num){
		try {
			String 	s =  "SELECT r_Id, GROUP_CONCAT(autoNum) as autoNum  FROM userinreq WHERE username = ? GROUP BY r_id ";
			PreparedStatement preparedStatement = conn.prepareStatement(s);
			preparedStatement.setInt(1, req_num);
			ResultSet rs = preparedStatement.executeQuery();
			while(rs.next()){
				String [] tmp = rs.getString("autoNum").split(",");
			for(String x: tmp)
				System.out.print(x);
		}} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;}
	


	
	public static void main(String[] args) {
      
		Sql_conn cc=new Sql_conn();
		cc.viewReq(300797099);
		// ArrayList<Late_requests>  l=(ArrayList<Late_requests>) cc.get_current_late_requests();
		
		//System.out.println(l.get(0).getLate_duration());
		// System.out.println(l.get(0).getReq_id());	
		// System.out.println(l.get(0).getStage_id());
	
	
	}}




